package edu.ucsd.cse135.gas.logic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import edu.ucsd.cse135.gas.logic.support.Database;

public class LetterOfRecommendation {

	private int id;
	private int applicant;
	private String firstName;
	private String name;
	private String mail;
	private String title;
	private String institute;
	private String letter;
	private String date;
	
	public String getMail() {
		return mail;
	}
	public void setMail(String mail) {
		this.mail = mail;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getApplicant() {
		return applicant;
	}
	public void setApplicant(int applicant) {
		this.applicant = applicant;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getInstitute() {
		return institute;
	}
	public void setInstitute(String institute) {
		this.institute = institute;
	}
	public String getLetter() {
		return letter;
	}
	public void setLetter(String letter) {
		this.letter = letter;
	}
	public String getDate() {
		return date;
	}
	public void setDate(String date) {
		this.date = date;
	}
	
	/** Method to generate a new key for external access. Return finished link **/
	
	public static String generateLink(int applicantId) {
		
		double key = Math.random()*123456789;
		
		int iKey = (int)key;
		
		try 
		{	
			Connection conn = Database.open();
			
			String sql = "INSERT INTO letter_of_recommendation_for_applicant " +
						 "VALUES (?, ?);";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, "" + iKey);
			preStmt.setInt(2, applicantId);
			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
			
			return "https://localhost:8443/GraduateAdmissionSystem/external/index.jsp?page=letterOfRecommendation&key=" + iKey;
		}
		catch(Exception e)
		{
			Database.close();
			return "Fail";
		}
	}
	
	/** Method to get all active links a user got **/
	
	public static ArrayList<String> getActiveLinks(int applicantId) {
		
		try
		{
			Connection conn = Database.open();
			
			PreparedStatement preStmt = conn.prepareStatement("SELECT key FROM letter_of_recommendation_for_applicant WHERE applicant = ?;");
			preStmt.setInt(1, applicantId);
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<String> linksAl = new ArrayList<String>();
			String link = "https://localhost:8443/GraduateAdmissionSystem/external/index.jsp?page=letterOfRecommendation&key=";
			
			while(rs.next())
			{
				linksAl.add(link + rs.getString("key"));
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return linksAl;
			
		}
		catch(Exception e)
		{
			Database.close();
			e.printStackTrace();
			return null;
		}
	}

	/** Get applicant id which matches to a specific key **/
	
	
	public static int getApplicantId(String key) {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "SELECT applicant FROM " +
						 "letter_of_recommendation_for_applicant " +
						 "WHERE key = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, key);
			
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			int applicant = rs.getInt("applicant");
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return applicant;
			
		}
		catch(Exception e)
		{
			Database.close();
			return -1;
		}
		
	}

	/** Method to write a new letter of recommendation to the database **/
	
	public boolean writeToDatabase() {
		
		try 
		{
			Connection conn = Database.open();
			
			String sql = "INSERT INTO letter_of_recommendation " +
						 "(applicant, first_name, name, title, mail, institute, letter) " +
						 "VALUES (?, ?, ?, ?, ?, ?, ?);";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			preStmt.setInt(1, this.getApplicant());
			preStmt.setString(2, this.getFirstName());
			preStmt.setString(3, this.getName());
			preStmt.setString(4, this.getTitle());
			preStmt.setString(5, this.getMail());
			preStmt.setString(6, this.getInstitute());
			preStmt.setString(7, this.getLetter());
			
			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
			
			return true;
		}
		catch(Exception e)
		{
			Database.close();
			e.printStackTrace();
			return false;
		}
	}

	/** Method to delete a used key **/
	
	public static boolean deleteKey(String key) {
		
		try
		{
			Connection conn = Database.open();
			
			PreparedStatement preStmt = conn.prepareStatement("DELETE FROM letter_of_recommendation_for_applicant WHERE key = ?;");
			preStmt.setString(1, key);
			
			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
			
			return true;
		}
		catch(Exception e)
		{
			Database.close();
			e.printStackTrace();
			return false;
		}
		
	}

	/** Method to validate whether a key is still valid or not **/
	
	public static boolean validateKey(String key) {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "SELECT applicant FROM " +
			 			 "letter_of_recommendation_for_applicant " +
			 			 "WHERE key = ?;";

			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, key);
			
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			rs.getInt("applicant");
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return true;
			
		}
		catch(Exception e)
		{
			Database.close();
			return false;
		}
	
	}
	
	/** Method to get all letters for a specific user **/
	
	public static ArrayList<LetterOfRecommendation> getLetters(int id) {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "SELECT * FROM letter_of_recommendation WHERE applicant = ?;";
			
			PreparedStatement preStmt =  conn.prepareStatement(sql);
			preStmt.setInt(1, id);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<LetterOfRecommendation> lorAl = new ArrayList<LetterOfRecommendation>();
			
			while(rs.next())
			{
				LetterOfRecommendation lor = new LetterOfRecommendation();
				
				lor.setApplicant(id);
				lor.setFirstName(rs.getString("first_name"));
				lor.setName(rs.getString("name"));
				lor.setMail(rs.getString("mail"));
				lor.setInstitute(rs.getString("institute"));
				lor.setTitle(rs.getString("title"));
				lor.setLetter(rs.getString("letter"));
				
				lorAl.add(lor);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return lorAl;
		}
		catch(Exception e)
		{
			Database.close();
			e.printStackTrace();
			return null;
		}
		
	}
}
